package com.zhiyou.daoImpl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import com.zhiyou.dao.HouseDao;
import com.zhiyou.model.House;
import com.zhiyou.util.DBUtils;
import com.zhiyou.util.PageBean;

public class HouseDaoImpl implements HouseDao {

	@Override
	public PageBean<House> findAll(int pc,int ps) {
		PageBean<House> pb = new PageBean<>();
		String sq = "select count(*) from house "; 
		Object ob[] = {};
		DBUtils db = new DBUtils();
		ResultSet r = db.select(sq, ob);
		pb.setPc(pc);
		pb.setPs(ps);
		int tr = 0;
		if(r==null){
		}else{
			try {
				while(r.next()){
					tr = r.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		pb.setTr(tr);
		String sql = "select * from house limit ?,? ";
		Object obj[] = {(pc-1)*ps ,ps};
		ResultSet rs = db.select(sql, obj);
		List<House> list = new ArrayList<>();
		if(rs == null ){
		}else{
			try {
				while(rs.next()){
					House house = new House(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getShort(5), rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getInt(10), rs.getInt(11), rs.getString(12), rs.getLong(13), rs.getInt(14), rs.getString(15), rs.getString(16), rs.getDate(17), rs.getDate(18));
					list.add(house);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		pb.setBeanList(list);
		db.close();
		return pb;
	}

	@Override
	public House findById(int id) {
		String sql = "select * from house where h_id = ? ";
		Object obj[] = {id};
		DBUtils db = new DBUtils();
		ResultSet rs = db.select(sql, obj);
		if(rs==null){
			db.close();
			return null;
		}else{
			try {
				while(rs.next()){
					House house = new House(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getShort(5), rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getInt(10), rs.getInt(11), rs.getString(12), rs.getLong(13), rs.getInt(14), rs.getString(15), rs.getString(16), rs.getDate(17), rs.getDate(18));
					db.close();
					return house;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		db.close();
		return null;
	}

	@Override
	public boolean delete(int id) {
		String sql = "update house set h_status = 5 where h_id = ?";
		Object obj[] = {id};
		DBUtils db = new DBUtils();
		int num = db.update(sql, obj);
		db.close();
		if(num==1){
			return true;
		}else{
			return false;
		}
	}

	@Override
	public boolean update(Object[] obj) {
		// TODO Auto-generated method stub
		String sql = "update house set h_area = ?,h_estate = ?,h_unitNumber = ?,h_floor = ?, h_roomNo = ?,h_acreage = ?,h_direction = ?,h_fitment = ?,h_isDoubleAir = ?,h_limit = ?,h_facility = ?,h_price = ?,h_status = ?,h_img = ?,h_address = ? where h_id = ?";
		DBUtils db = new DBUtils();
		int num = db.update(sql, obj);
		System.out.println(Arrays.toString(obj));
		System.out.println(num);
		db.close();
		if(num==1){
			return true;
		}else{
			return false;
		}
	}

	@Override
	public boolean add(Object[] obj) {
		// TODO Auto-generated method stub
		String sql = "insert into house value(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,now(),now())";
		DBUtils db = new DBUtils();
		int num = db.update(sql, obj);
		db.close();
		if(num==1){
			return true;
		}else{
			return false;
		}
	}

	@Override
	public int[] dels(int[] id) {
		// TODO Auto-generated method stub
		String sql = "update house set h_status = 5 where h_id = ?";
		DBUtils db = new DBUtils();
		int nums[] = db.dels(sql, id);
		db.close();
		return nums;
	}

	@Override
	public PageBean<House> find(String area, String isdouble, String acreage, String status, int pc, int ps) {
		DBUtils db = new DBUtils();
		PageBean<House> pb = new PageBean<>();
		pb.setPc(pc);
		pb.setPs(ps);
		StringBuffer sq = new StringBuffer("select count(*) from house where 1=1 ");
		List<Object> list = new ArrayList<>();
		if(area!=null&&!area.trim().isEmpty()){
			sq.append(" and h_area like ? ");
			list.add("%"+area+"%");
		}
		if(!isdouble.equals("-1")){
			sq.append(" and h_isDoubleAir = ? ");
			list.add(Integer.parseInt(isdouble));
		}
		if(acreage!=null&&!acreage.trim().isEmpty()){
			sq.append(" and h_acreage > ? ");
			list.add(Double.parseDouble(acreage));
		}
		if(!status.equals("-1")){
			sq.append(" and h_status = ? ");
			list.add(Integer.parseInt(status));
		} 
		int tr = 0;
		ResultSet r = db.select(sq.toString(), list.toArray());
		if(r==null){
			pb.setTr(0);
		}else{ 
			try {
				while(r.next()){
					tr = r.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			pb.setTr(tr);
		}
		StringBuffer sql = new StringBuffer(sq.toString().replace("count(*)", "*"));
		sql.append(" limit ?,?");
		list.add((pc-1)*ps);
		list.add(ps);
		List<House> list1 = new ArrayList<>();
		ResultSet rs = db.select(sql.toString(), list.toArray());
		if (rs==null) {
			
		}else{
			try {
				while(rs.next()){
					House house = new House(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getShort(5), rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getInt(10), rs.getInt(11), rs.getString(12), rs.getLong(13), rs.getInt(14), rs.getString(15), rs.getString(16), rs.getDate(17), rs.getDate(18));
					list1.add(house);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		pb.setBeanList(list1);
		db.close();
		return pb;
	} 
	
}
